package com.itic.appbase.applications.exports.utils;

import java.util.Calendar;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import com.itic.appbase.framework.utils.DateHelper;

/**
 * POI工具类
 * <p>
 * Company: itic
 * </p>
 * 
 * @author: shijun
 * @date: 2015年5月29日 下午3:04:34
 * @version: V1.0
 */
public class PoiHelper {

    /**
     * 创建Excel工作薄
     * 
     * @return HSSFWorkbook
     */
    public static HSSFWorkbook createWorkBook() {
        return new HSSFWorkbook();
    }

    /**
     * 创建Excel工作表
     * 
     * @see #createWorkBook()
     * @param workbook
     *            工作薄
     * @return HSSFSheet
     */
    public static HSSFSheet createSheet(HSSFWorkbook workbook) {
        return workbook.createSheet();
    }

    /**
     * 创建Excel工作表
     * 
     * @see #createWorkBook()
     * @param workbook
     *            工作薄
     * @param sheetName
     *            工作表名称
     * @return HSSFSheet
     */
    public static HSSFSheet createSheet(HSSFWorkbook workbook, String sheetName) {
        return workbook.createSheet(sheetName);
    }
    /**
     * 获取sheet
     * </br>根据sheet名称获取sheet
     * @param workbook
     *      @see #createWorkBook()
     * @param sheetName
     * @return
     */
    public static HSSFSheet getSheetByName(HSSFWorkbook workbook, String sheetName){
        return workbook.getSheet(sheetName);
    }
    /**
     * 创建Excel行
     * 
     * @see #createSheet(HSSFWorkbook workbook)
     * @param sheet
     *            工作表
     * @param rownum
     *            行坐标
     * @return HSSFRow
     */
    public static HSSFRow createRow(HSSFSheet sheet, int rownum) {
        return sheet.createRow(rownum);
    }

    /**
     * 获取Excel行
     * 
     * @param sheet
     * @see #createSheet(HSSFWorkbook)
     * @param rownum
     *            行索引
     * @return HSSFRow
     */
    public static HSSFRow getRow(HSSFSheet sheet, int rownum) {
        return sheet.getRow(rownum);
    }

    /**
     * 创建Excel单元格
     * 
     * @see #createRow(HSSFSheet sheet,int rownum)
     * @param row
     *            行
     * @param column
     *            列坐标
     * @return HSSFCell
     */
    public static HSSFCell createCell(HSSFRow row, int column) {
        return row.createCell(column);
    }

    /**
     * 获取Excel单元格
     * 
     * @param row
     * @see #createRow(HSSFSheet, int)
     * @param column
     *            列坐标
     * @return HSSFCell
     */
    public static HSSFCell getCell(HSSFRow row, int column) {
        return row.getCell(column);
    }

    /**
     * 合并单元格
     * 
     * @see #createSheet(HSSFWorkbook)
     * @see #setRangeAddress(int, int, int, int)
     * @param sheet
     *            Excel工作表
     * @param cellRangeAddress
     */
    public static void mergedRegion(HSSFSheet sheet,
            CellRangeAddress cellRangeAddress) {
        // 合并单元格
        sheet.addMergedRegion(cellRangeAddress);
    }

    /**
     * 设置合并区域边框
     * 
     * @see #createWorkBook()
     * @see #createSheet(HSSFWorkbook)
     * @see #setRangeAddress(int, int, int, int)
     * @param workBook
     *            Excel工作薄
     * @param sheet
     *            Excel工作表
     * @param cellRangeAddress
     */
    public static void setRangeBorder(HSSFWorkbook workBook, HSSFSheet sheet,
            CellRangeAddress cellRangeAddress) {
        // 设置合并后区域样式
        RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, cellRangeAddress,
                sheet, workBook);
        RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, cellRangeAddress,
                sheet, workBook);
        RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, cellRangeAddress,
                sheet, workBook);
        RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, cellRangeAddress,
                sheet, workBook);
    }

    /**
     * 设置合并单元格区域
     * 
     * @param firstRow
     *            开始行坐标
     * @param lastRow
     *            结束行坐标
     * @param firstCol
     *            开始列坐标
     * @param lastCol
     *            结束列坐标
     * @return CellRangeAddress
     */
    public static CellRangeAddress setRangeAddress(int firstRow, int lastRow,
            int firstCol, int lastCol) {
        return new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
    }

    /**
     * 设置指定列的宽度
     * 
     * @param sheet
     * @see #createSheet(HSSFWorkbook)
     * @param columnIndex
     * @param width
     *            宽度
     */
    public static void setColumnWidth(HSSFSheet sheet, int columnIndex,
            int width) {
        sheet.setColumnWidth(columnIndex, width * 256);
    }

    /**
     * 设置指定行的高度
     * 
     * @param row
     * @see #createRow(HSSFSheet, int)
     * @param height
     *            高度
     */
    public static void setRowHeight(HSSFRow row, int height) {
        row.setHeight((short) (height * 20));
    }

    /**
     * 单元格赋值
     * <p>
     * 根据传递参数的类型，给单元格赋值
     * </p>
     * 
     * @see #createCell(HSSFRow, int)
     * @param cell
     *            单元格对象
     * @param obj
     *            值
     */
    public static void setCellValue(HSSFCell cell, Object obj) {
        String cellValue = "";
        if (obj instanceof String) {
            cellValue = (String) obj;
        }else if (obj instanceof Date) {
            cellValue = DateHelper.formatDate((Date) obj);//默认为yyyy-MM-dd
        } else if (obj instanceof Calendar) {
            cellValue = DateHelper.formatDate(((Calendar) obj).getTime());//默认为yyyy-MM-dd
        } else if (obj instanceof Double) {
            cellValue = String.valueOf((Double) obj);
        } else if (obj instanceof Boolean) {
            cellValue = String.valueOf((Boolean) obj);
        } else {
            cellValue = String.valueOf(obj);
        }
        HSSFRichTextString text = new HSSFRichTextString(cellValue);
        cell.setCellValue(text);
    }

    /**
     * 单元格赋值并设置样式
     * <p>
     * 根据传递参数的类型，给单元格赋值并添加单元格样式
     * </p>
     * 
     * @see #createCell(HSSFRow, int)
     * @see #createCellStyle(HSSFWorkbook, HSSFFont)
     * @param cell
     *            单元格对象
     * @param obj
     *            值
     * @param cellStyle
     *            单元格样式
     */
    public static void setCellValue(HSSFCell cell, Object obj,
            HSSFCellStyle cellStyle) {
        setCellValue(cell, obj);
        cell.setCellStyle(cellStyle);
    }

    /**
     * 创建单元格样式
     * 
     * @see #createWorkBook()
     * @see #createFont(HSSFWorkbook workbook)
     * @param workbook
     *            工作薄
     * @param font
     *            设置的字体样式
     * @return HSSFCellStyle
     */
    public static HSSFCellStyle createCellStyle(HSSFWorkbook workbook,
            HSSFFont font) {
        // 设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置不自动换行;
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        
        //设置单元格的类型为文本类型
        HSSFDataFormat format = workbook.createDataFormat();
        style.setDataFormat(format.getFormat("@"));

        return style;
    }

    /**
     * 创建字体
     * <p>
     * 创建字体，默认宋体，不加粗，大小为12
     * </p>
     * 
     * @see #createWorkBook()
     * @param workbook
     * @return HSSFFont
     */
    public static HSSFFont creatFont(HSSFWorkbook workbook) {
        // 创建字体
        HSSFFont font = workbook.createFont();
        // 设置字体
        font.setFontName("宋体");
        // 不加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 设置字体大小
        font.setFontHeightInPoints((short) 12);
        return font;
    }
}
